<?php

namespace App\Data;

use App\Models\HospitalImportBigchild;
use App\Models\HospitalImportChild;
use App\Models\HospitalImportPregn;
use App\Models\HospitalUserFamily;
use App\Models\ImportFamily;
use Illuminate\Support\Facades\DB;

class SignData
{

    /**
     * 36月龄儿童管辖总数
     * @param $hospital_id
     * @return int
     */
    public static function childTotle($hospital_id)
    {
        return HospitalImportChild::query()
            ->where('hospital_id', $hospital_id)
            ->where('status', 1)
            ->count();
    }


    /**
     * 36月龄儿童签约明细
     * @param $hospital_id
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public static function childSign($hospital_id){

        return ImportFamily::query()
            ->where('hospital_id', $hospital_id)
            ->where('type', 1)
            ->groupBy('import_id');
    }
    /**
     * 36月龄儿童签约总数
     * @param $hospital_id
     * @return int
     */
    public static function childSignTotal($hospital_id){
        $sql = "SET sql_mode = (SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));";
        DB::statement($sql);
        return self::childSign($hospital_id)->get()->count();
    }

    /**
     * 36月龄儿童今日签约数
     * @param $hospital_id
     * @return int
     */
    public static function childToday($hospital_id)
    {
        return HospitalUserFamily::query()->where('eb_hospital_user_family.hospital_id',$hospital_id)
            ->join('eb_family', "eb_hospital_user_family.family_id", '=', "eb_family.id")
            ->where('eb_hospital_user_family.status',1)
            ->where('eb_hospital_user_family.type',1)
            ->where('eb_hospital_user_family.created_at',">",date('Y-m-d'))
            ->where('eb_family.birthday',">",date('Y-m-d',strtotime("-3 year")))
            ->count();
    }

    /**
     * 家医儿童管辖总数
     * @param $hospital_id
     * @return int
     */
    public static function bigChildTotle($hospital_id){
        return HospitalImportBigchild::query()
            ->where('hospital_id', $hospital_id)
            ->where('status', 1)
            ->count();
    }

    /**
     * 家医儿童签约明细
     * @param $hospital_id
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public static function bigChildSign($hospital_id){
        return ImportFamily::query()
            ->where('hospital_id', $hospital_id)
            ->where('type', 3)
            ->groupBy('import_id');
    }

    /**
     * 家医儿童签约数
     * @param $hospital_id
     * @return int
     */
    public static function bigChildSignTotal($hospital_id){
        return self::bigChildSign($hospital_id)->get()->count();
    }

    public static function bigChildToday($hospital_id){
        return HospitalUserFamily::query()->where('eb_hospital_user_family.hospital_id',$hospital_id)
            ->join('eb_family', "eb_hospital_user_family.family_id", '=', "eb_family.id")
            ->where('eb_hospital_user_family.status',1)
            ->where('eb_hospital_user_family.type',1)
            ->where('eb_hospital_user_family.created_at',">",date('Y-m-d'))
            ->where('eb_family.birthday',">",date('Y-m-d',strtotime("-7 year")))
            ->count();
    }

    /**
     * 孕产妇管辖总数
     * @param $hospital_id
     * @return int
     */
    public static function pregTotle($hospital_id){
        return HospitalImportPregn::query()
            ->where('hospital_id', $hospital_id)
            ->where('status', 1)
            ->count();
    }

    /**
     * 孕产妇签约明细
     * @param $hospital_id
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public static function pregSign($hospital_id){
        return ImportFamily::query()
            ->where('hospital_id', $hospital_id)
            ->where('type', 2)
            ->groupBy('import_id');
    }

    /**
     * 孕产妇签约数
     * @param $hospital_id
     * @return int
     */
    public static function pregSignTotal($hospital_id){
        return self::pregSign($hospital_id)->get()->count();
    }

    /**
     * 孕产妇今日签约数
     * @param $hospital_id
     * @return int
     */
    public static function pregToday($hospital_id)
    {
        return HospitalUserFamily::query()->where('eb_hospital_user_family.hospital_id',$hospital_id)
            ->join('eb_family', "eb_hospital_user_family.family_id", '=', "eb_family.id")
            ->where('eb_hospital_user_family.status',1)
            ->where('eb_hospital_user_family.type',2)
            ->where('eb_hospital_user_family.created_at',">",date('Y-m-d'))
            ->count();
    }


}
